Namespace persistent

    Public Class Test_Card_Deactivater
        Public Shared Function CheckMSISDN(ByVal p_msisdn As String) As String
            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(1) As String
            Dim l_Parameters_Val_Array(1) As String
            l_ProcedureName = "eu_wrknd.TEST_CARD_DEACTIVATER.CheckMSISDN"
            l_Parameters_Def_Array(0) = "p_msisdn"
            l_Parameters_Def_Array(1) = "p_result"
            l_Parameters_Val_Array(0) = p_msisdn
            l_Parameters_Val_Array(1) = ""
            myDBConn.ORAProcedure_INOUT(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
            Return l_Parameters_Val_Array(1)
        End Function

        Public Shared Function Deactivate_Order(ByVal p_MSISDN As String, ByVal p_DeactDate As String, ByVal p_user As String) As String
            Dim sql As String
            sql = "insert into eu_wrknd.TEST_CARD_DEACTIVATER_REQS(ID, CUSTOMER_ID, CO_ID, PROCESSED, TO_BE_DEACTIVATED_DATE, PROCESS_DATE, MODIFIED_BY, MODIFY_DATE) values (eu_wrknd.TEST_CARD_DEACTIVATER_REQS_seq.nextval, fn_msisdn_to_custid('[TAG1]'), fn_msisdn_to_coid('[TAG1]'), 'O', to_date('[TAG2]', 'dd.mm.yyyy'), null, '[TAG3]', sysdate) "
            sql = sql.Replace("[TAG1]", p_MSISDN)
            sql = sql.Replace("[TAG2]", p_DeactDate)
            sql = sql.Replace("[TAG3]", p_user)
            myDBConn.ORAExecute(sql)
            Return "OK"
        End Function

        Public Shared Function CheckDateFormat(ByVal p_date As String) As String
            Dim sql As String
            sql = "select to_date('[TAG1]', 'dd.mm.yyyy') from dual"
            sql = sql.Replace("[TAG1]", p_date)
            Try
                Dim dummy As String
                dummy = myDBConn.ORAScalar(sql)
                Return "OK"
            Catch ex As Exception
                Return "ERROR: Invalid date format, please use DD.MM.YYYY format!"
            End Try
        End Function

        Public Shared Function ReportAll() As DataTable
            Dim sql As String
            Dim l_dt As DataTable
            sql = "select CUSTOMER_ID, CO_ID, '[' || fn_coid_to_msisdn(co_id) || ']' MSISDN , decode(PROCESSED, 'O', 'Ordered', 'Y', 'Processed', 'C', 'Cancelled', 'Unknown Status') STATUS, to_char(TO_BE_DEACTIVATED_DATE, 'dd.mm.yyyy') TO_BE_DEACTIVATED_DATE, PROCESS_DATE, MODIFIED_BY, MODIFY_DATE from eu_wrknd.TEST_CARD_DEACTIVATER_REQS order by id "
            Return myDBConn.myORATable(sql)
        End Function

        Public Shared Function GetNonProcessed() As DataTable
            Dim sql As String
            Dim l_dt As DataTable
            sql = "select ID, CUSTOMER_ID, CO_ID, fn_coid_to_msisdn(co_id) MSISDN, to_char(TO_BE_DEACTIVATED_DATE, 'dd.mm.yyyy') TO_BE_DEACTIVATED_DATE, MODIFIED_BY, MODIFY_DATE from eu_wrknd.TEST_CARD_DEACTIVATER_REQS where PROCESSED = 'O' order by id "
            Return myDBConn.myORATable(sql)
        End Function

        Public Shared Function DelPreviousRequest(ByVal p_id As String) As String
            Dim sql As String
            sql = "update eu_wrknd.TEST_CARD_DEACTIVATER_REQS set PROCESSED = 'C' where id = [TAG1] "
            sql = sql.Replace("[TAG1]", p_id)
            myDBConn.ORAExecute(sql)
            Return "OK"
        End Function

    End Class

End Namespace
